CREATE procedure sp_asi_FullAddress  
  @ADDRESS_1 varchar(40)=null,
  @ADDRESS_2 varchar(40)=null,
  @ADDRESS_3 varchar(40)=null,
  @CITY varchar(40)=null,
  @STATE_PROVINCE varchar(15)=null,
  @ZIP varchar(10)=null,
  @COUNTRY varchar(25)=null
  as
  declare
  @formula varchar(255),
  @code int,
  @addrlen int
  BEGIN
  if datalength(@ADDRESS_1) = 0 and datalength(@ADDRESS_2) = 0 and datalength(@ADDRESS_3) = 0 and datalength(@CITY) = 0
and datalength(@STATE_PROVINCE) = 0 and datalength(@ZIP) = 0 and datalength(@COUNTRY) = 0
      begin
        select @formula=''
        select FULL_ADDRESS=@formula
        return
      end
  select @code = NULL
  select @code=ADDRESS_FORMAT from Country_Names where upper(COUNTRY)=upper(@COUNTRY)
  select @code=isnull(@code,0)
  if @code=0  
    begin  if (not exists (select * from Country_Addr_Layouts where ADDRESS_FORMAT=0))
    insert into Country_Addr_Layouts (ADDRESS_FORMAT, ADDRESS_LAYOUT, FORMULA)
  values (0, 'ADDRESS_1[N]ADDRESS_2[N]ADDRESS_3[N]CITY[C][S]STATE_PROVINCE[S]ZIP[N][U]COUNTRY',
  'ADDRESS_1[N]ADDRESS_2[N]CITY[C][S]STATE_PROVINCE[S]ZIP[N][U]COUNTRY')
    end
  begin   
  select @formula=FORMULA from Country_Addr_Layouts where ADDRESS_FORMAT=@code
  while charindex('[N]',@formula)>0
    begin
    select @formula=stuff(@formula,charindex('[N]',@formula),3,char(13))
    end
  while charindex('[S]',@formula)>0
    begin
    select @formula=stuff(@formula,charindex('[S]',@formula),3,' ')
    end
  while charindex('[C]',@formula)>0
    begin
    select @formula=stuff(@formula,charindex('[C]',@formula),3,',')
    end
  if charindex('CITY',@formula)>0
    select @formula=stuff(@formula,charindex('CITY',@formula),4,'_CITY')
  if charindex('ZIP',@formula)>0
    select @formula=stuff(@formula,charindex('ZIP',@formula),3,'_ZIP')
  if charindex('COUNTRY',@formula)>0
    select @formula=stuff(@formula,charindex('COUNTRY',@formula),7,'_COUNTRY')
  if charindex('[U]ADDRESS_1',@formula)>0
    select @formula=stuff(@formula,charindex('[U]ADDRESS_1',@formula),12,upper(@ADDRESS_1))
  if charindex('[U]ADDRESS_2',@formula)>0
    select @formula=stuff(@formula,charindex('[U]ADDRESS_2',@formula),12,upper(@ADDRESS_2))
  if charindex('[U]ADDRESS_3',@formula)>0
    select @formula=stuff(@formula,charindex('[U]ADDRESS_3',@formula),12,upper(@ADDRESS_3))
  if charindex('[U]_CITY',@formula)>0
    begin
    if charindex('[U]_CITY,',@formula)>0
      begin
      if datalength (@STATE_PROVINCE) = 0  
        select @formula=stuff(@formula,charindex('[U]_CITY,',@formula),9,'[U]_CITY')
      end
    select @formula=stuff(@formula,charindex('[U]_CITY',@formula),8,upper(@CITY) )
    end
  if charindex('[U]STATE_PROVINCE',@formula)>0
    select @formula=stuff(@formula,charindex('[U]STATE_PROVINCE',@formula),17,upper(@STATE_PROVINCE))
  if charindex('[U]_ZIP',@formula)>0
    select @formula=stuff(@formula,charindex('[U]_ZIP',@formula),7,upper(@ZIP))
  if charindex('[U]_COUNTRY',@formula)>0
    select @formula=stuff(@formula,charindex('[U]_COUNTRY',@formula),11,upper(@COUNTRY))
  if charindex('ADDRESS_1',@formula)>0
    select @formula=stuff(@formula,charindex('ADDRESS_1',@formula),9,@ADDRESS_1)
  if charindex('ADDRESS_2',@formula)>0
    select @formula=stuff(@formula,charindex('ADDRESS_2',@formula),9,@ADDRESS_2)
if charindex('ADDRESS_3',@formula)>0
    select @formula=stuff(@formula,charindex('ADDRESS_3',@formula),9,@ADDRESS_3)
  if charindex('_CITY',@formula)>0
    begin
    if charindex('_CITY,',@formula)>0
      begin
      if datalength (@STATE_PROVINCE) = 0  
        select @formula=stuff(@formula,charindex('_CITY,',@formula),6,'_CITY')
      end
    select @formula=stuff(@formula,charindex('_CITY',@formula),5,@CITY)
    end
  if charindex('STATE_PROVINCE',@formula)>0
    select @formula=stuff(@formula,charindex('STATE_PROVINCE',@formula),14,@STATE_PROVINCE)
  if charindex('_ZIP',@formula)>0
    select @formula=stuff(@formula,charindex('_ZIP',@formula),4,@ZIP)
  if charindex('_COUNTRY',@formula)>0
    select @formula=stuff(@formula,charindex('_COUNTRY',@formula),8,@COUNTRY)
  if charindex('US1',@formula)>0
    select @formula=stuff(@formula,charindex('US1',@formula),3,'')
  if charindex('US2',@formula)>0
    select @formula=stuff(@formula,charindex('US2',@formula),3,'')
  while charindex(char(13)+' ',@formula)>0      
    begin        
    select @formula=stuff(@formula,charindex(char(13)+' ',@formula),2,char(13))      
    end
  while charindex(char(13)+',',@formula)>0      
    begin        
    select @formula=stuff(@formula,charindex(char(13)+',',@formula),2,char(13))      
    end
  while charindex(char(13)+char(13),@formula)>0      
    begin        
    select @formula=stuff(@formula,charindex(char(13)+char(13),@formula),2,char(13))      
    end    
  while charindex(char(13)+' '+char(13),@formula)>0      
    begin        
    select @formula=stuff(@formula,charindex(char(13)+' '+char(13),@formula),3,char(13))      
    end
  select @addrlen=datalength (@formula)
  while substring (@formula, @addrlen, 1)=char(13)
         select @formula=substring(@formula, 1, @addrlen-1)
  select @formula  FULL_ADDRESS
  return
  end
  END

GO
GRANT EXECUTE ON  [dbo].[sp_asi_FullAddress] TO [IMIS]
GO